/**
 * User: zsquirrel
 * Date: 2020/4/30
 * Time: 9:51 上午
 */
package com.cskaoyan.mall.dao;

import com.cskaoyan.mall.model.*;
import com.cskaoyan.mall.model.bo.GoodsParamBO;
import com.cskaoyan.mall.model.bo.OrderedGoodsInfoBO;
import com.cskaoyan.mall.model.bo.ReplyBO;
import com.cskaoyan.mall.model.bo.SpecBO;
import com.cskaoyan.mall.model.enmu.CommentRating;
import com.cskaoyan.mall.model.enmu.MsgState;
import com.cskaoyan.mall.model.vo.GoodsCommentVO;
import com.cskaoyan.mall.model.vo.GoodsInfoMsgVO;
import com.cskaoyan.mall.model.vo.MessageVO;
import com.cskaoyan.mall.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class GoodsDaoImpl implements GoodsDao {
    @Override
    public List<Type> getType() {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Type> typeList = null;
        try {
            typeList = runner.query("select * from type", new BeanListHandler<Type>(Type.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return typeList;
    }

    @Override
    public List<Goods> getGoodsByType(String typeId) {
        Map<String, Object> map = getDynamicSqlParams(typeId);
        String sql = (String) map.get("sql");
        List<Object> params = (List<Object>) map.get("params");
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Goods> goodsList = null;
        try {
            goodsList = runner.query(sql,
                    new BeanListHandler<Goods>(Goods.class), params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsList;
    }

    private Map<String, Object> getDynamicSqlParams(String typeId) {
        String sql = "select * from goods where 1 = 1 ";
        List<Object> params = new ArrayList<>();
        if(!"-1".equals(typeId)){
            sql = sql + " and typeId = ?";
            params.add(typeId);
        }
        Map<String, Object> map = new HashMap<>();
        map.put("sql", sql);
        map.put("params", params);
        return map;
    }

    @Override
    public int addGoods(GoodsParamBO goodsParamBO) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("insert into goods values (null,?,?,?,?,?,?)",
                    goodsParamBO.getImg(),
                    goodsParamBO.getName(),
                    goodsParamBO.getPrice(),
                    goodsParamBO.getTypeId(),
                    goodsParamBO.getStockNum(),
                    goodsParamBO.getDesc());
            //插入完成之后，goods会有id，需要将该id，赋值给spec表的goodsId
            return insertId();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int insertId(){
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        BigInteger number = null;
        try {
            number = runner.query("select last_insert_id()", new ScalarHandler<>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return number.intValue();
    }

    @Override
    public void addSpecList(int goodsId, List<Spec> specList) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        for (Spec spec : specList) {
            try {
                runner.update("insert into spec values (null,?,?,?,?)",
                        spec.getSpecName(),
                        spec.getStockNum(),
                        spec.getUnitPrice(),
                        goodsId);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void deleteType(String typeId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("delete from spec where id = ?",typeId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Spec> getSpecs(String goodsId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Spec> specs = null;
        try {
            specs = runner.query("select * from spec where goodsId = ?", new BeanListHandler<Spec>(Spec.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return specs;
    }

    @Override
    public Goods getGoodsById(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Goods goods = null;
        try {
            goods = runner.query("select * from goods where id = ?", new BeanHandler<>(Goods.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    /**
     * 这个时候新增规格的时候应当有specName不能相同的校验
     * @param specBO
     */
    @Override
    public void deleteSpec(SpecBO specBO) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("delete from spec where goodsId = ? and specName = ?",
                    specBO.getGoodsId(),
                    specBO.getSpecName());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void addSpec(Spec spec) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("insert into spec values (null,?,?,?,?)",
                    spec.getSpecName(),
                    spec.getStockNum(),
                    spec.getUnitPrice(),
                    spec.getGoodsId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateGoods(GoodsParamBO paramBO) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("update goods set name = ?, typeId = ?, img = ?, `desc` = ?, price = ?, stockNum = ? where id = ?",
                    paramBO.getName(),
                    paramBO.getTypeId(),
                    paramBO.getImg(),
                    paramBO.getDesc(),
                    paramBO.getPrice(),
                    paramBO.getStockNum(),
                    paramBO.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateSpecList(List<Spec> updateSpecs) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        for (Spec spec : updateSpecs) {
            try {
                runner.update("update spec set specName = ?, stockNum = ?, unitPrice = ?, goodsId = ? where id = ?",
                        spec.getSpecName(),
                        spec.getStockNum(),
                        spec.getUnitPrice(),
                        spec.getGoodsId(),
                        spec.getId());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void deleteSpec(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("delete from spec where goodsId = ?",id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteGoods(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("delete from goods where id = ?",id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据当前的商品规格id，查询出该商品的其他所有规格
     * @param goodsDetailId
     * @return
     */
    @Override
    public List<Spec> getSiblingSpecs(Integer goodsDetailId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Spec> specs = null;
        try {
            specs = runner.query("select * from spec where goodsId = (select goodsId from spec where id = ?)",
                    new BeanListHandler<Spec>(Spec.class), goodsDetailId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return specs;
    }

    @Override
    public List<MessageVO> getMsg(Integer stateId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<MessageVO> messageList = null;
        try {
            messageList = runner.query("select m.*, g.name as goodsName, u.nickname as userName from message m, goods g, user u where m.goodsId = g.id and m.userId = u.id and m.state = ?", new BeanListHandler<MessageVO>(MessageVO.class), stateId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messageList;
    }

    @Override
    public void reply(ReplyBO replyBO) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("update message set replyContent = ?,replytime = now(),state = ? where id = ?",
                    replyBO.getContent(),
                    MsgState.REPLIED.getCode(),
                    replyBO.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Goods> searchGoods(String keyword) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<Goods> goods = null;
        try {
            goods = runner.query("select * from goods where name like ? or `desc` like ?", new BeanListHandler<Goods>(Goods.class),
                    "%" + keyword + "%","%" + keyword + "%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    @Override
    public List<GoodsInfoMsgVO> getAllMsgs(String goodsId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<GoodsInfoMsgVO> infoMsgVOS = null;
        try {
            infoMsgVOS = runner.query("select m.id,m.content,m.createtime as time,m.replytime,m.replyContent,u.nickname as asker from message m, user u,goods g where m.userId = u.id and m.goodsId = g.id and g.id = ?",
                    new BeanListHandler<GoodsInfoMsgVO>(GoodsInfoMsgVO.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return infoMsgVOS;
    }

    @Override
    public List<GoodsCommentVO> getGoodsComment(String goodsId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<GoodsCommentVO> commentVOList = null;
        try {
            commentVOList = runner.query("select c.score,c.id,c.specName,c.comment,c.commentTime,c.userId,u.nickname from comment c,user u where c.userId = u.id and c.goodsId = ?",
                    new BeanListHandler<GoodsCommentVO>(GoodsCommentVO.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return commentVOList;
    }

    @Override
    public Integer getTotalCommentsCount(String goodsId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Long count = null;
        try {
            count = runner.query("select count(id) from comment where goodsId = ?",
                    new ScalarHandler<>(), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count.intValue();
    }

    @Override
    public Integer getPositiveCommentCount(String goodsId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        Long count = null;
        try {
            count = runner.query("select count(id) from comment where score >= ? and goodsId = ?",
                    new ScalarHandler<>(),
                    CommentRating.POSITIVE_COMMENT.getScore(),
                    goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count.intValue();
    }

    @Override
    public void askGoodsMsg(Integer userId, String msg, String goodsId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("insert into message (id,userId,goodsId,content,state,createtime) values (null,?,?,?,?,now())",
                    userId,
                    goodsId,
                    msg,
                    MsgState.NO_REPLY.getCode());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public OrderedGoodsInfoBO getOrderedGoodsInfo(Integer goodsDetailId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        OrderedGoodsInfoBO goodsInfoBO = null;
        try {
            goodsInfoBO = runner.query("select g.id as goodsId, g.name as goods,s.specName as spec,s.id as goodsDetailId,g.img,s.unitPrice from goods g, spec s where g.id = s.goodsId and s.id = ?",
                    new BeanHandler<>(OrderedGoodsInfoBO.class), goodsDetailId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsInfoBO;
    }

    @Override
    public void updateStockNum(Integer goodsNum, Integer goodsDetailId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("update spec set stockNum = stockNum - ? where id = ?",
                    goodsNum,
                    goodsDetailId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
